package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

public class DBSupplier implements IFDBSupplier{
    private  Connection con;
    /** Creates a new instance of DBSupplier */
    public DBSupplier() {
      con = DBConnection.getInstance().getDBcon();
    }
    
  //Implements the methods from the interface
    // get all Suppliers
    public ArrayList<Supplier> listAllSuppliers(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    //get one Supplier having the SupplierId
    public Supplier findSupplier(int SupplierId, boolean retriveAssociation)
    {   String wClause = "  id = '" + SupplierId + "'";
        return singleWhere(wClause, retriveAssociation);
    }
    
    public String getSupplier(int supplierId)
    {
    	String wClause = "supplierId = '" + supplierId + "'";
		String supplier = singleWhereSupplier(wClause);
		return supplier;
    }
     //insert a new Supplier
    @Override
    public int insertSupplier(Supplier sup) throws Exception
    {  //call to get the next SupplierId number
        int nextSupplierId = DBGetMax.getMaxId("Select max(SupplierId) from Supplier");
        nextSupplierId = nextSupplierId + 1;
        System.out.println("next SupplierId = " +  nextSupplierId);
  
        // inserts a new supplier
       int rc = -1;
	   String query="INSERT INTO Supplier(supplierId, supplierName, country, phoneNumber, email)  VALUES('"+
	  		nextSupplierId  + "','"  +
		    sup.getSupplierName()  + "','"  +
            sup.getCountry() + "','" +
            sup.getPhoneNumber() + "','" +
            sup.getEmail() + "','";
                     

       System.out.println("insert : " + query);
      try{
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Supplier ikke oprettet");
          throw new Exception ("Supplier is not inserted correct");
       }
       return(rc);
    }

    // this method deletes a supplier found on the suppliers id
	public int deleteSupplier(int supplierId)
{
	int rc = -1;

	String query = "DELETE FROM supplier WHERE supplierId = '" + supplierId + "'";
	System.out.println(query);
	try
	{ // delete from product
		Statement stmt = con.createStatement();
		stmt.setQueryTimeout(5);
		rc = stmt.executeUpdate(query);
		stmt.close();
	}// end try
	catch (Exception ex)
	{
		System.out.println("Delete exception in supplier db: " + ex);
	}
	return (rc);
}
	// this method updates an existing supplier
	public int updateSupplier(Supplier sup)
	{
		Supplier supObj  = sup;
		int rc=-1;

		// here the new values are to be put in
		String query="UPDATE Supplier SET "+
		 	  "supplierName ='"+ supObj.getSupplierName() + "', " +
                          "Country ='"+ supObj.getCountry() + "', " +
                          "phoneNumber ='"+ supObj.getPhoneNumber() + "', " +
                          "email = '" + supObj.getEmail() + "',";
                System.out.println("Update query:" + query);
  		try{ // update Supplier
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in Supplier db: "+ex);
	  	}
		return(rc);
	}
	
	
	//private methods
    //michWere is used whenever we want to select more than one Supplier
	
	 
	private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
        
        // creates a local arraylist
	    ArrayList<Supplier> list = new ArrayList<Supplier>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the Supplier from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	 	// start while
		while( results.next() ){
			//creates a new supplier object
	     	 Supplier supObj = new Supplier();
	     	 // the supplier object get its values form the buildSupplier method
		 supObj = buildSupplier(results);
		 // the supplier object is added to the local arraylist
                 list.add(supObj);	
		}//end while
                 stmt.close();     
                 if(retrieveAssociation)
                 {  
                	 IFDBProduct dbProduct = new DBProduct();
                	 // here the products on the supplier are to be retrieved 
                     for(Supplier supObj : list){
                    	 supObj.addProductsArray(dbProduct.listAllProductsFromSupplier(supObj.getSupplierId(), false));

                     }
                 }//end if   
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one Supplier 	
	private Supplier singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Supplier supObj = new Supplier();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the Supplier from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            supObj = buildSupplier(results);
                            //assocaition is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {  
                            	// the products are to be retrieved
                            	IFDBProduct dbProduct = new DBProduct();
                            	supObj.addProductsArray(dbProduct.listAllProductsFromSupplier(supObj.getSupplierId(), false));

                           
                           
                           
                            }
			}
                        else{ //no Supplier was found
                            supObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		// returns the object
		return supObj;
	}
	
	private String singleWhereSupplier(String wClause)
	{
		ResultSet results = null;
		String supplier = "";
		
		String query = "Select * from Supplier where " + wClause;
		System.out.println(query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			results.next();
			
			supplier = results.getString("supplierName");
			stmt.close();
		}
		
		
		catch (Exception e)
		{
			supplier = null;
		}
		
		
		return supplier;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT SupplierId, supplierName, country, phoneNumber, email  FROM Supplier";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an Supplier object
	private Supplier buildSupplier(ResultSet results)
      {   Supplier supObj = new Supplier();
          try{ // the values to the supplier are to be put in here
        	  	supObj.setSupplierId(results.getInt("SupplierId"));
                supObj.setSupplierName(results.getString("supplierName"));
                supObj.setCountry(results.getString("country"));
                supObj.setPhoneNumber(results.getInt("phoneNumber"));
                supObj.setEmail(results.getString("email"));
      }
         catch(Exception e)
         {
             System.out.println("error in building the Supplier object");
         }
         // returns object
         return supObj;
      }
 
}  
    

